package service.impl;

import entity.CourseDO;
import entity.GradeDO;
import req.CourseRequest;
import req.GradeRequest;
import res.CourseDTO;
import res.GradeDTO;
import service.GradeService;
import util.DBUtil;
//import util.ExcelUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

public class GradeServiceImpI implements GradeService {

    @Override
    public GradeDTO retrieveGrade(GradeRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from grade ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where ID = " + request.getSearchKey().trim());
        }

        sql.append("order by sno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        GradeDTO returnDTO = new GradeDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));
            ArrayList list = new ArrayList<>(returnDTO.getData());
            sql.setLength(0);
            sql.append("select count(*) from grade ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where ID = " + request.getSearchKey().trim());
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            //ExcelUtil.exportExcel(数据集合，表名，类class文件); // list, 学生表 ， GradeDO.class
            //ExcelUtil.exportExcel(list,"grade",GradeDO.class,); // list, 学生表 ， GradeDO.class
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int ID = rs.getInt("ID");
            int sno = rs.getInt("sno");
            int cno = rs.getInt("cno");
            String grade = rs.getString("grades");
            String level = rs.getString("level");
            String semester = rs.getString("semester");
            oneRecord.addElement(ID);
            oneRecord.addElement(sno);
            oneRecord.addElement(cno);
            oneRecord.addElement(grade);
            oneRecord.addElement(level);

            oneRecord.addElement(semester);

            data.addElement(oneRecord);
        }
        return data;
    }

    @Override
    public boolean add(GradeDO gradeDO) {
        StringBuilder sql = new StringBuilder();

        sql.append(" insert into grade ");
        sql.append(" values(?,?,?,?,?,?)");

        PreparedStatement ps = null;
        Connection conn = null;

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, gradeDO.getId());
            ps.setInt(2, gradeDO.getSno());
            ps.setInt(3, gradeDO.getCno());
            ps.setString(4, gradeDO.getGrade());
            ps.setString(5, gradeDO.getLevel());
            ps.setString(6, gradeDO.getSemester());

            return ps.executeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }


    @Override
    public GradeDO getById(int selectCourseId) {
        StringBuilder sql = new StringBuilder("select * from grade where ID = ? ");
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        GradeDO gradeDO = new GradeDO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, selectCourseId);
            rs = ps.executeQuery();
            while (rs.next()) {
                //Vector<Object> oneRecord = new Vector<>();
                int ID = rs.getInt("ID");
                int cno = rs.getInt("cno");
                int sno = rs.getInt("sno");
                String grade = rs.getString("grades");
                String level = rs.getString("level");
                String semester = rs.getString("semester");
                gradeDO.setId(ID);
                gradeDO.setSno(sno);
                gradeDO.setCno(cno);
                gradeDO.setGrade(grade);
                gradeDO.setLevel(level);
                gradeDO.setSemester(semester);
            }
            return gradeDO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean update(GradeDO gradeDO) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" update grade set grades =  ? , semester = ? ,level = ? ");
        sql.append(" where cno = ? and sno = ? ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            //ps.setString(1,studentDO.getNo());
            ps.setString(1, gradeDO.getGrade());

            ps.setString(2,gradeDO.getSemester());
            ps.setString(3,gradeDO.getLevel());

            ps.setInt(4, gradeDO.getCno());
            ps.setInt(5, gradeDO.getSno());

            return ps.executeUpdate() == 1;
            //查寻记录
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
        //return false;
    }

    @Override
    public boolean delete(int[] selectCourseIds) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" delete from grade where ID in ( ");
        int length = selectCourseIds.length;
        for (int i = 0; i < selectCourseIds.length; i++) {
            if (i == length - 1) {
                sql.append(" ? ");
            } else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                //设置参数
                ps.setInt(i + 1, selectCourseIds[i]);
            }
            return ps.executeUpdate() == length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }


    public GradeDO count(){

        StringBuilder sql = new StringBuilder();
        sql.append("select * from grade ");
//        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
//            sql.append(" where ID = ? '%" + request.getSearchKey().trim() + "%'");
//        }

        //sql.append("order by sno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        GradeDTO returnDTO = new GradeDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));
            ArrayList list = new ArrayList<>(returnDTO.getData());
            sql.setLength(0);
            sql.append("select count(*) from grade ");
//            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
//                sql.append(" where ID = ? '%" + request.getSearchKey().trim() + "%'");
//            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            //ExcelUtil.exportExcel(数据集合，表名，类class文件); // list, 学生表 ， GradeDO.class
            //ExcelUtil.exportExcel(list,"grade",GradeDO.class,); // list, 学生表 ， GradeDO.class
            //return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }
}
